package a_o2odata_deal.utils

import a_aa_amainpackage.a_o2odata_deal.config.config.{months, years}
import org.apache.spark.sql.SQLContext
import org.apache.spark.{SparkConf, SparkContext}

/**
  * @ Auther: o2o-rd-0008
  * @ Date:   2020/4/10 09:59
  * @ Param:  ${PARAM}
  * @ Description: 
  */
object anchor_shop {

  def main(args: Array[String]): Unit = {
    val conf = new SparkConf()
    conf.setAppName(s"${this.getClass.getSimpleName}")
    conf.set("spark.debug.maxToStringFields", "500")
    //conf.setMaster("local[*]")
    conf.set("es.nodes", "192.168.1.157")
    conf.set("es.port", "9200")
    conf.set("cluster.name", "O2OElastic")
    conf.set("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
    conf.set("spark.sql.caseSensitive", "true")
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    sc.setLogLevel("WARN")
    sc.hadoopConfiguration.set("fs.s3a.access.key", "GAO7EO9FWKPJ8WFCQDME")
    sc.hadoopConfiguration.set("fs.s3a.secret.key", "LZ0xaHBSYKHaJ9ECDbX9f7zin79UZkXfGoNapRPL")
    //sc.hadoopConfiguration.set("fs.s3a.endpoint", "https://obs.cn-north-1.myhuaweicloud.com")



    //加载主播数据
    val anchor1 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2019/1/all_anchor").selectExpr("anchorId")
    val anchor2 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2019/2/all_anchor").selectExpr("anchorId")
    val anchor3 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2019/3/all_anchor").selectExpr("anchorId")
    val anchor4 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2019/4/all_anchor").selectExpr("anchorId")
    val anchor5 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2019/5/all_anchor").selectExpr("anchorId")
    val anchor6 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2019/6/all_anchor").selectExpr("anchorId")
    val anchor7 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2019/7/all_anchor").selectExpr("anchorId")
    val anchor8 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2019/8/all_anchor").selectExpr("anchorId")
    val anchor9 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2019/9/all_anchor").selectExpr("anchorId")
    val anchor10 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2019/10/all_anchor").selectExpr("anchorId")
    val anchor11 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2019/11/all_anchor").selectExpr("anchorId")
    val anchor12 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2019/12/all_anchor").selectExpr("anchorId")
    val anchor13 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2020/1/all_anchor").selectExpr("anchorId")
    val anchor14 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2020/2/all_anchor").selectExpr("anchorId")
    val anchor15 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2020/3/all_anchor").selectExpr("anchorId")

    anchor1.union(anchor2).union(anchor3).union(anchor4).union(anchor5).union(anchor6).union(anchor7).union(anchor8).union(anchor9)
      .union(anchor10).union(anchor11).union(anchor12).union(anchor13).union(anchor14).union(anchor15)
      .dropDuplicates().createOrReplaceTempView("anchor")

    val tmall1 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/1/tmall/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val tmall2 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/2/tmall/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val tmall3 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/3/tmall/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val tmall4 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/4/tmall/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val tmall5 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/5/tmall/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val tmall6 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/6/tmall/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val tmall7 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/7/tmall/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val tmall8 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/8/tmall/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val tmall9 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/9/tmall/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val tmall10 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/10/tmall/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val tmall11 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/11/zhibo_finally")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val tmall12 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/12/zhibo_finally")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val tmall13 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2020/1/zhibo_finally")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val tmall14 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2020/2/zhibo_finally")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val tmall15 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/2020/3/zhibo_finally")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")

    tmall1.union(tmall2).union(tmall3).union(tmall4).union(tmall5).union(tmall6).union(tmall7).union(tmall8).union(tmall9)
      .union(tmall10).union(tmall11).union(tmall12).union(tmall13).union(tmall14).union(tmall15)
      .dropDuplicates("userId").createOrReplaceTempView("tmall_data")

    val taobao1 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/1/taobao/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val taobao2 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/2/taobao/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val taobao3 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/3/taobao/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val taobao4 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/4/taobao/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val taobao5 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/5/taobao/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val taobao6 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/6/taobao/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val taobao7 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/7/taobao/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val taobao8 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/8/taobao/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val taobao9 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/9/taobao/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val taobao10 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/10/taobao/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val taobao11 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/11/taobao/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val taobao12 = sqlContext.read.orc(s"s3a://dws-data/g_data/2019/12/taobao/")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val taobao13 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/taobao/2020/1/zhibo_finally")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val taobao14 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/taobao/2020/2/zhibo_finally")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")
    val taobao15 = sqlContext.read.orc(s"s3a://o2o-dataproces-group/panzonghao/taobao/2020/3/zhibo_finally")
      .selectExpr("platformId","platformName","shopId","shopName","shopUrl","userId").dropDuplicates("userId")

    taobao1.union(taobao2).union(taobao3).union(taobao4).union(taobao5).union(taobao6).union(taobao7).union(taobao8).union(taobao9)
      .union(taobao10).union(taobao11).union(taobao12).union(taobao13).union(taobao14).union(taobao15)
      .dropDuplicates("userId").createOrReplaceTempView("taobao_data")


    //计算天猫店铺信息
    val result1 = sqlContext.sql(
      s"""
         |select
         |concat(concat(t2.anchorId,t1.shopId),t1.platformId) as major_key,
         |t2.anchorId,
         |t1.shopId,
         |t1.shopName,
         |t1.shopUrl,
         |t1.platformId,
         |t1.platformName
         |from tmall_data t1
         |inner join
         |anchor t2
         |on t1.userId=t2.anchorId
       """.stripMargin)

    //计算淘宝店铺信息
    val result2 = sqlContext.sql(
      s"""
         |select
         |concat(concat(t2.anchorId,t1.shopId),t1.platformId) as major_key,
         |t2.anchorId,
         |t1.shopId,
         |t1.shopName,
         |t1.shopUrl,
         |t1.platformId,
         |t1.platformName
         |from taobao_data t1
         |inner join
         |anchor t2
         |on t1.userId=t2.anchorId
       """.stripMargin)

    result1.selectExpr("major_key","anchorId","shopId","shopName","shopUrl","platformId","platformName").union(
      result2.selectExpr("major_key","anchorId","shopId","shopName","shopUrl","platformId","platformName")
    ).repartition(4).write.orc(s"s3a://o2o-dataproces-group/panzonghao/tmall/${years}/${months}/anchor_shop_v3")

  }

}
